package com.sea.finance.service.impl;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.ObjectMetadata;
import com.codecrane.core.web.BatchImportUtils;
import com.sea.finance.entity.Vt;
import com.sea.finance.entity.VtFile;
import com.sea.finance.entity.VtRelated;
import com.sea.finance.mapper.VtMapper;
import com.sea.finance.service.VtFileService;
import com.sea.finance.service.VtRelatedService;
import com.sea.finance.service.VtService;

import com.sea.supply.entity.Buy;
import com.sea.supply.entity.PdfFile;
import com.sea.supply.entity.ProducePayment;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;


/**
 * @author 陈鸿飞
 */
@Service
public class VtServiceImpl implements VtService {
    @Autowired
    private VtMapper vtMapper;

    @Autowired
    private VtFileService vtFileService;
    @Autowired
    private VtRelatedService vtRelatedService;

    @Value("${db.url}")
    private String JDBC_URL;

    @Value("${db.username}")
    private String USER;

    @Value("${db.password}")
    private String PASS;




    private static final int BATCH_SIZE = 5000;




    private void processZipFile(URL url,VtFile vtFile) throws Exception {

        JDBC_URL = JDBC_URL + "&rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true";

        // 1. 先将ZIP文件下载到本地临时文件
        File tempZipFile = null;
        try {
            // 创建临时文件（Windows环境下需确保有写入权限）
            tempZipFile = File.createTempFile("vt_zip_", ".zip");
            System.out.println("临时文件路径: " + tempZipFile.getAbsolutePath());

            // 使用URLConnection设置超时，避免网络僵死
            URLConnection connection = url.openConnection();
            connection.setConnectTimeout(30000); // 30秒连接超时
            connection.setReadTimeout(60000);    // 60秒读取超时

            // 下载文件到本地
            try (InputStream is = connection.getInputStream();
                 FileOutputStream fos = new FileOutputStream(tempZipFile);
                 BufferedInputStream bis = new BufferedInputStream(is);
                 BufferedOutputStream bos = new BufferedOutputStream(fos)) {

                byte[] buffer = new byte[8192];
                int len;
                long totalBytes = 0;
                while ((len = bis.read(buffer)) != -1) {
                    bos.write(buffer, 0, len);
                    totalBytes += len;
                }
                System.out.println("ZIP文件下载完成，大小: " + totalBytes + "字节");
            }

            // 2. 从本地文件读取ZIP内容（避免网络流问题）
            try (FileInputStream fis = new FileInputStream(tempZipFile);
                 BufferedInputStream bis = new BufferedInputStream(fis);
                 ZipInputStream zis = new ZipInputStream(bis);
                 Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS)) {

                conn.setAutoCommit(false);
                ZipEntry entry;
                int entryCount = 0;

                while ((entry = zis.getNextEntry()) != null) {
                    entryCount++;
                    try {
                        if (entry.isDirectory() || !entry.getName().toLowerCase().endsWith(".xlsx")) {
                            continue;
                        }

                        System.out.printf("处理条目: %s (大小: %d字节)%n",
                                entry.getName(), entry.getSize());

                        // 读取条目内容到内存
                        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
                            byte[] buffer = new byte[8192];
                            int len;
                            while ((len = zis.read(buffer)) != -1) {
                                baos.write(buffer, 0, len);
                            }
                            baos.flush();

                            // 处理Excel数据
                            processExcelData(new ByteArrayInputStream(baos.toByteArray()),
                                    conn, vtFile, entry.getName());
                        }

                    } catch (EOFException e) {
                        System.err.printf("条目 %s 读取异常: %s%n", entry.getName(), e.getMessage());
                        continue; // 跳过错误条目
                    } finally {
                        zis.closeEntry(); // 确保关闭条目
                    }
                }
                conn.commit();
                System.out.println("所有条目处理完成，共" + entryCount + "个");
            }

        } catch (Exception e) {
            System.err.println("处理失败: " + e.getMessage());
            throw e;
        } finally {
            // 清理临时文件
            if (tempZipFile != null && tempZipFile.exists()) {
                if (tempZipFile.delete()) {
                    System.out.println("临时文件已删除");
                } else {
                    System.err.println("临时文件删除失败: " + tempZipFile.getAbsolutePath());
                }
            }
        }
    }



    private static final String[] HEADERS = {
            "period","shop_name", "brand_name", "country_name","file_name",
            "settlement_id", "settlement_start_date", "settlement_end_date",
            "deposit_date", "total_amount", "currency", "transaction_type",
            "order_id", "merchant_order_id", "adjustment_id", "shipment_id",
            "marketplace_name", "amount_type", "amount_description", "amount",
            "fulfillment_id", "posted_date", "posted_date_time", "order_item_code",
            "merchant_order_item_id", "merchant_adjustment_item_id", "sku",
            "quantity_purchased", "promotion_id","fee_name","listing_name"
    };

    private static final int EXTRA_FIELDS_COUNT = 5; // 新增的5个字段
    private  void processExcelData(InputStream excelStream, Connection conn,VtFile vtFile,String fileName) throws Exception {
        Map<String,String> listingMap=new HashMap<>();
        List<VtRelated> vList = vtRelatedService.findByCnd(null);
        if(null != vList && vList.size() !=0){
            for(VtRelated vtRelated:vList){
                listingMap.put(vtRelated.getSku(),vtRelated.getListingName());
            }
        }
        String period=vtFile.getPeriod();
        String shopName=vtFile.getShopName();
        String brandName=vtFile.getBrandName();
        String countryName=vtFile.getCountryName();
        String sql = "INSERT INTO a_vt (" + String.join(", ", HEADERS) + ") VALUES (" +
                String.join(", ", Collections.nCopies(HEADERS.length, "?")) + ")";

        // 添加计数器
        AtomicInteger counter = new AtomicInteger(0);
        long startTime = System.currentTimeMillis();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            EasyExcel.read(excelStream, new PageReadListener<Map<Integer, String>>(dataList -> {
                try {
                    for (Map<Integer, String> data : dataList) {
                        // 设置额外字段
                        pstmt.setString(1, period);
                        pstmt.setString(2, shopName);
                        pstmt.setString(3, brandName);
                        pstmt.setString(4, countryName);
                        pstmt.setString(5, fileName);

                        // 设置原有字段（Excel数据列索引从0开始）
                        for (int i = 0; i < HEADERS.length - EXTRA_FIELDS_COUNT; i++) {
                            // 跳过fee_name和listing_name字段，后续单独处理
                            if (i + EXTRA_FIELDS_COUNT < HEADERS.length - 2) {
                                pstmt.setObject(i + EXTRA_FIELDS_COUNT + 1, data.get(i));
                            }
                        }

                        // 处理fee_name字段：拼接transaction_type, amount_type和amount_description
                        // 根据实际数据修正索引值
                        String transactionType = data.get(6);  // 修正为正确的索引
                        String amountType = data.get(12);      // 修正为正确的索引
                        String amountDescription = data.get(13); // 修正为正确的索引

                        // 拼接fee_name
                        String feeName = String.join("-", transactionType, amountType, amountDescription);

                        // 设置fee_name字段（最后一个字段）
                        pstmt.setString(HEADERS.length - 1, feeName); // 倒数第二个字段
                        String sku = data.get(21); // Excel中第21列（索引20）是SKU
                        String listingName = listingMap.getOrDefault(sku, ""); // 不存在则为空字符串
                        if(StringUtils.isNotBlank(listingName)) {
                            pstmt.setString(HEADERS.length, listingName); // 最后一个字段
                        }else{
                            pstmt.setString(HEADERS.length, null); // 最后一个字段
                        }
                        pstmt.addBatch();

                        // 更新计数器并打印进度
                        int current = counter.incrementAndGet();
                        if (current % 1000 == 0) {  // 每1000条打印一次
                            long elapsed = (System.currentTimeMillis() - startTime) / 1000;
                            System.out.printf("已导入: %,d 条, 耗时: %d秒, 当前速度: %,d 条/秒%n",
                                    current, elapsed, current / Math.max(1, elapsed));
                        }
                    }
                    pstmt.executeBatch();
                } catch (SQLException e) {
                    throw new RuntimeException("数据库操作失败", e);
                }
            }, BATCH_SIZE)).sheet().doRead();

            // 导入完成后打印总结
            System.out.printf("导入完成! 总计导入: %,d 条数据%n", counter.get());
        }
    }



    @Override
    public String dealFile(Integer id) {
        VtFile vtFile = vtFileService.findById(id);
        long startTime = System.currentTimeMillis();
        if(StringUtils.isNotBlank(vtFile.getFileUrl())){


            try {
                URL url = new URL(vtFile.getFileUrl());
                processZipFile(url,vtFile);
            } catch (Exception e) {
                e.printStackTrace();
                return e.getMessage();
            }

            System.out.println("导入完成，耗时: " + (System.currentTimeMillis() - startTime)/1000 + "秒");
        }


        return null;
    }

    @Override
    public Integer delete(String period, String shopName, String brandName, String countryName) {
        return vtMapper.delete(period,shopName,brandName,countryName);
    }

    @Override
    public Integer deleteTitle() {
        return vtMapper.deleteTitle();
    }

    @Override
    public Integer deleteUSPeriod(String countryName) {
        return vtMapper.deleteUSPeriod(countryName);
    }

    @Override
    public Integer deletEUPeriod(String countryName) {
        return vtMapper.deletEUPeriod(countryName);
    }

    @Override
    public void deleteByNonAmazonOrder(String orderId) {
        vtMapper.deleteByNonAmazonOrder(orderId);
    }

    @Override
    public List<Vt> findByFeeName(String feeName) {
        return vtMapper.findByFeeName(feeName);
    }


    @Override
    public void  importDealSku(InputStream inputStream, String fileName) throws Exception {
        Workbook workBook = BatchImportUtils.getWorkBook(inputStream, fileName);
        List<Sheet> sheets = BatchImportUtils.getSheets(workBook);
        if (null != sheets && sheets.size() > 0) {
            Sheet sheet = sheets.get(0);
            List<Vt> list = BatchImportUtils.iteratorForVtDealSku(sheet);
            if (null == list || list.size() == 0) {
                throw new Exception("请至少新增一条明细！");
            } else {
                for (Vt vt : list) {
                    if(StringUtils.isBlank(vt.getSku())) {
                       vt.setSku(null);
                    }
                    if(StringUtils.isBlank(vt.getSku())) {
                        vt.setListing_name(null);
                    }
                    usave(vt);
                }
            }
        }
    }

    @Override
    public void importDealAmazonOrder(InputStream inputStream, String fileName) throws Exception {
        Workbook workBook = BatchImportUtils.getWorkBook(inputStream, fileName);
        List<Sheet> sheets = BatchImportUtils.getSheets(workBook);
        if (null != sheets && sheets.size() > 0) {
            Sheet sheet = sheets.get(0);
            List<Vt> list = BatchImportUtils.iteratorForVtDealAmazonOrder(sheet);
            if (null == list || list.size() == 0) {
                throw new Exception("请至少新增一条明细！");
            } else {
                for (Vt vt : list) {
                    if(StringUtils.isNotBlank(vt.getOrder_id())){
                        deleteByNonAmazonOrder(vt.getOrder_id());
                    }
                    //updateAmazonByOrderId(vt);
                }
            }
        }
    }

    @Override
    public void importAmazonOrder(InputStream inputStream, String fileName) throws Exception {
        Workbook workBook = BatchImportUtils.getWorkBook(inputStream, fileName);
        List<Sheet> sheets = BatchImportUtils.getSheets(workBook);
        if (null != sheets && sheets.size() > 0) {
            Sheet sheet = sheets.get(0);
            List<Vt> list = BatchImportUtils.iteratorForVtDealAmazonOrder(sheet);
            if (null == list || list.size() == 0) {
                throw new Exception("请至少新增一条明细！");
            } else {
                for (Vt vt : list) {
                    if(StringUtils.isNotBlank(vt.getOrder_id())){
                        vtMapper.updateAmazonByOrderId(vt.getOrder_id());
                    }

                }
            }
        }
    }

    @Override
    public String uploadVtFile(String fileName,InputStream newPdfInputStream) {
        OSSClient ossClient=new OSSClient("http://oss-cn-beijing.aliyuncs.com",
                "LTAI4G55LJEk8nARtrjYngou",
                "XPbdMs0xrRGAsmKVm9VYC8bSTTuFhl");

        ObjectMetadata metadata = new ObjectMetadata();
        metadata.setContentType("application/octet-stream");

        ossClient.putObject("cozylux-v2", fileName, newPdfInputStream,metadata);
        Date expiration = new Date(System.currentTimeMillis() + 3600L * 1000 * 24 * 365 * 50);
        String url = ossClient.generatePresignedUrl("cozylux-v2", fileName, expiration).toString();

        // 关闭OSSClient。
        ossClient.shutdown();
        return url;
    }

    @Override
    public Integer save(Vt vt) {
        return vtMapper.insert(vt);
    }

    @Override
    public Integer reload() {
        return vtMapper.reload();
    }

    @Override
    public Integer updateFeeName() {
        return vtMapper.updateFeeName();
    }

    @Override
    public Integer updateFnSku() {
        return vtMapper.updateFnSku();
    }

    @Override
    public Integer updateListingName() {
        return vtMapper.updateListingName();
    }

    @Override
    public List<Vt> findByDealSku(Vt vt) {
        return vtMapper.findByDealSku(vt);
    }

    @Override
    public Integer usave(Vt vt) {
        return vtMapper.update(vt);
    }

    @Override
    public List<Map<String, Object>> findByReport(Vt vt) {
        return vtMapper.findByReport(vt);
    }

    @Override
    public List<Map<String, Object>> findNullByReport(Vt vt) {
        return vtMapper.findNullByReport(vt);
    }

    @Override
    public List<Map<String, Object>> findListingByReport(Vt vt) {
        return vtMapper.findListingByReport(vt);
    }

    @Override
    public List<Vt> findByNonAmazon(Vt vt) {
        return vtMapper.findByNonAmazon(vt);
    }
}
